/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.negocio.pedagogico;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import src.com.dados.pedagogico.TurmaDao;

/**
 *
 * @author Giane
 */
public class ConsultarTurma {

    public ArrayList<String> consultaTurma(String codigo, String i_idioma, String n_nivel, String professor) throws SQLException {

        System.out.print("AQ " + codigo + i_idioma + n_nivel + professor);
        String select_idm = null;
        if (!codigo.isEmpty()) {
            select_idm = "SELECT tur_codigo,idm_descricao,pes_nome FROM tur_turma, idm_idioma,prof_professor,pes_pessoa where prof_professor_prof_id = prof_id and pes_pessoa_pes_id = pes_id and idm_idioma_idm_id = idm_id and tur_codigo = " + codigo + ";";
        } else {
            String where = "";
            if (!i_idioma.equals(" ")) {
                where = where + " and idm_descricao like '" + i_idioma + "'";
            } else if (!professor.isEmpty()) {
                where = where + " and pes_nome like '" + professor + "'";
            } else if (!n_nivel.isEmpty()) {
                where = where + " and nvl_nome like '" + n_nivel + "'";
            }
            select_idm = "SELECT tur_codigo,idm_descricao,pes_nome FROM tur_turma, idm_idioma,prof_professor,pes_pessoa,nvl_nivel where prof_professor_prof_id = prof_id and pes_pessoa_pes_id = prof_id and idm_idioma_idm_id = idm_id and nvl_nivel_nvl_id = nvl_id " + where;
        }

        System.out.println(select_idm);

        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idm);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result.add(rs.getString(1));
            result.add(rs.getString(2));
            result.add(rs.getString(3));
        }

        conexao.close();
        return result;





    }

    public ArrayList<String> consultaTurma2(String codigo, String i_idioma, String n_nivel, String professor) throws SQLException {
        //System.out.print("AQ " + codigo + i_idioma + n_nivel + professor);
        String select_idm = null;

        select_idm = "SELECT tur_codigo, tur_vagas,tur_dt_inicio,tur_dt_fim,tur_dia1,tur_dia2,tur_hr_inico,tur_hr_fim,idm_descricao,pes_nome,nvl_nome FROM nvl_nivel, tur_turma, idm_idioma,prof_professor,pes_pessoa where prof_professor_prof_id = prof_id and pes_pessoa_pes_id = pes_id and idm_idioma_idm_id = idm_id and nvl_nivel_nvl_id = nvl_id and tur_codigo = " + codigo + ";";


        System.out.println(select_idm);

        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idm);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            for (int i = 1; i < 12; i++) {
                result.add(rs.getString(i));
            }
        }


        conexao.close();
        return result;
    }

    public String consTurma(String nome) throws SQLException {
        TurmaDao id = new TurmaDao();
        return id.id_turma(nome);


    }
}
